﻿using System;
using System.Collections.Generic;
using System.Text;
using Model = SQLServerHealthCheck.Model;
using SQLServerHealthCheck.Model;
using System.Linq;
using System.Data.SqlClient;
using SMO = Microsoft.SqlServer.Management.Smo;
using WMI = Microsoft.SqlServer.Management.Smo.Wmi;
using System.Windows.Forms;
using System.Collections;
using System.IO;
using SQLServerHealthCheck.SQLServer.IssueTypes;
using System.Data.SqlTypes;

namespace SQLServerHealthCheck.SQLServer
{
    public static class CollectDatabasesData
    {
        public static List<Exception> errors;
        public static ArrayList arrDatabaseCheck = new ArrayList();
        public static List<Exception> Collect(List<String> SQLServerInstances)
        {
            try
            {
                errors = new List<Exception>();

                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());

                DeleteDatabasesAndFiles();

                CollectDatabasesAndFiles(dc, SQLServerInstances);
            }
            catch (System.Security.SecurityException ex1)
            {
                MessageBox.Show("You don't have permission to delete file in path " + Application.StartupPath, "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return errors;
        }

        private static void DeleteDatabasesAndFiles()
        {

            using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]))
            {
                cn.Open();
                String sql = @" 
                            DELETE FROM DatabaseFile
                            DELETE FROM [Database]
                            
                                

";


                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.ExecuteNonQuery();





            }

        }

        public static void CollectDatabasesAndFiles(HealthCheckDataContext dc, List<String> SQLServerInstances)
        {
            foreach (String sqlServerInstance in SQLServerInstances)
            {

                FillDatabasesAndFiles(dc, sqlServerInstance);


            }
        }

        private static void FillDatabasesAndFiles(HealthCheckDataContext dc, String sqlServerInstance)
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.

                String[] parts = sqlServerInstance.ToLower().Split('\\');
                String serverName = parts[0];


                Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance);
                    if (service != null)
                    {

                        if (CollectServerAndServiceData.IsSQLServerAccessible(@"Server=" + sqlServerInstance + ";database=master;Integrated Security=True"))
                        {
                            using (SqlConnection cn = new SqlConnection(@"Server=" + sqlServerInstance + ";Database=master;Integrated Security=True"))
                            {
                                cn.Open();
                                String sql = GetSQLOverviewDatabasesAndFiles();

                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();



                                while (rd.Read())
                                {


                                    Database db = null;

                                    if (service.Databases.Count(d => d.DatabaseName == rd[1].ToString()) == 0)
                                    {
                                        db = new Database();

                                        db.DatabaseName = rd[1].ToString();
                                        db.Collation = rd.IsDBNull(2) ? "" : rd[2].ToString();
                                        db.Owner = rd[3].ToString();
                                        db.RecoveryModel = rd[4].ToString();
                                        db.State = rd[5].ToString();
                                        db.CompatibilityLevel = rd[6].ToString();

                                        SqlBoolean sqlBool = rd.GetBoolean(7);

                                        if (!sqlBool.IsNull)
                                        {
                                            db.IsAutoShrink = sqlBool.Value;
                                        }


                                        db.DatabaseVersion = rd[8].ToString();
                                        db.CreateDate = Convert.ToDateTime(rd[9].ToString());

                                        db.LatestFullBackup = rd.IsDBNull(17) ? (DateTime?)null : rd.GetDateTime(17);
                                        db.LatestDifferentialBackup = rd.IsDBNull(18) ? (DateTime?)null : rd.GetDateTime(18);
                                        db.LatestLogBackup = rd.IsDBNull(19) ? (DateTime?)null : rd.GetDateTime(19);

                                        service.Databases.Add(db);

                                    }
                                    else
                                    {
                                        db = service.Databases.First(d => d.DatabaseName == rd[1].ToString());
                                    }

                                    int offset = 10;

                                    if (!rd.IsDBNull(offset))
                                    {
                                        DatabaseFile dbfile = new DatabaseFile();

                                        dbfile.File = rd[offset].ToString();
                                        dbfile.FileGroup = rd[offset + 1].ToString();
                                        dbfile.Drive = rd[offset + 2].ToString();
                                        dbfile.SizeInMb = rd.GetDecimal(offset + 3);

                                        SqlBoolean sqlBool = rd.GetBoolean(offset + 4);

                                        if (!sqlBool.IsNull)
                                        {
                                            dbfile.AutoGrowth = sqlBool.Value;
                                        }


                                        dbfile.MaxSize = rd.GetInt32(offset + 5);
                                        dbfile.GrowthPercent = rd.GetInt32(offset + 6);

                                        db.DatabaseFiles.Add(dbfile);
                                    }
                                }

                                rd.Close();


                            }

                            dc.SubmitChanges();

                            //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Completed for " + service.InstanceName.ToString());

                        }

                        else
                        {
                            //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                            arrDatabaseCheck.Add(sqlServerInstance);
                        }


                    }
                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " Some error occured in Database Check.Please check exception file for details");
            }



        }

        private static String GetSQLOverviewDatabasesAndFiles()
        {
            String sql = @"

	
                                BEGIN TRY	
	                                Create table #Database_BackupDetails(
	  
		                                DatabaseName varchar(200),
		                                LatestFullBackup [datetime] NULL,
		                                LatestDifferentialBackup [datetime] NULL,
		                                LatestLogBackup [datetime] NULL
	                                )

	                                Create table #Database(
	                                     [DatabaseID] int,
		                                DatabaseName varchar(200),
		                                Collation varchar(500) NULL,
		                                Create_date [datetime] NULL,
		                                [Owner] varchar(200) NULL,
		                                RecoveryModel varchar(20) NULL,
		                                State varchar(100) NULL,
		                                [CompatibilityLevel] [varchar](50) NULL,
                                        IsAutoShrink bit NULL)
	
	                                Create table #Database_FileDetails(
		                                DatabaseName varchar(200),
		                                [File] varchar(150) NULL, 
		                                FileGroup varchar(150) NULL, 
		                                Drive varchar(50) NULL,
		                                SizeInMb decimal NULL,
                                        AutoGrowth bit NULL,
                                        MaxSize int NULL,
                                        GrowthPercent int NULL
                                        
	                                )
                                END TRY
                                BEGIN CATCH
                                END CATCH

                                declare @query varchar(8000)
                                Set @query=''
                                DECLARE 
	                                @databaseID int,
	                                @database varchar(150),
	                                @collation varchar(500),
	                                @owner varchar(200),
	                                @recoveryModel varchar(20),
	                                @state varchar(100),
	                                @create_date [datetime] ,
	                                @LatestFullBackup [datetime] ,
	                                @LatestDifferentialBackup [datetime] ,
	                                @LatestLogBackup [datetime] ,
	                                @CompatibilityLevel [varchar](50),
                                    @IsAutoShrink [BIT]
	
		
                                DECLARE databases_cursor CURSOR FOR
	                                SELECT d.name , d.database_id , d.collation_name,d.Create_date, p.name as Owner, d.recovery_model_desc, d.state_desc, d.compatibility_level,d.is_auto_shrink_on
	                                FROM sys.databases d LEFT OUTER JOIN sys.server_principals p  ON d.owner_sid=p.sid 



                                OPEN databases_cursor

                                FETCH NEXT FROM databases_cursor INTO @database, @databaseid, @collation,@create_date ,@owner, @recoveryModel, @state, @CompatibilityLevel,@IsAutoShrink

                                WHILE @@FETCH_STATUS = 0
                                 BEGIN
                                Set @query='

                                INSERT INTO #Database
                                (	
	                                [DatabaseID],
	                                DatabaseName,
	                                Collation ,	
	                                Create_date ,
	                                [Owner],
	                                RecoveryModel,
	                                State,
	                                [CompatibilityLevel],
                                    IsAutoShrink 
                                )
                                VALUES
                                (
	                                ' + cast(@databaseid as varchar(100)) + ',
	                                '''+@database + ''',
	                                '''+ ISNULL( @collation,'')  + ''',
	                                '''+ cast(@create_date as varchar(100)) + ''',
	                                '''+@owner + ''',
	                                '''+@recoveryModel+ ''',
	                                '''+@state + ''',
	                                '''+@CompatibilityLevel + ''',
                                    '''+ CAST(@IsAutoShrink AS CHAR(1)) + '''
                                )

	                                insert into #Database_BackupDetails( DatabaseName,	LatestFullBackup,LatestDifferentialBackup,LatestLogBackup) 
	
	
	                                 SELECT [Database] , [Full] AS LatestFullBackup, [Differential] AS LatestDifferentialBackup, [Log] as LatestLogBackupDate
				                                FROM
				                                (
					                                SELECT 
						                                DATABASE_NAME [Database],
						                                BackupType=
							                                CASE 
								                                WHEN [TYPE]=''D'' THEN ''Full'' 
								                                WHEN [TYPE]=''I'' THEN ''Differential'' 
								                                WHEN [TYPE]=''L'' THEN ''Log''
								                                WHEN [TYPE]=''F'' THEN ''File / Filegroup''
								                                WHEN [TYPE]=''G'' THEN ''Differential file''
								                                WHEN [TYPE]=''P'' THEN ''Partial''
								                                WHEN [TYPE]=''Q'' THEN ''Differential partial''
							                                END,
						                                cast(MAX(BACKUP_DATE) as varchar(100)) BACKUP_DATE
					                                FROM 
						                                (
							                                SELECT 
								                                ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,
								                                A.BACKUP_FINISH_DATE AS BACKUP_DATE, 
								                                B.PHYSICAL_DEVICE_NAME,
								                                A.MEDIA_SET_ID,
								                                A.DATABASE_NAME,
								                                [TYPE]
							                                FROM 
								                                MSDB.DBO.BACKUPSET A JOIN 
								                                MSDB.DBO.BACKUPMEDIAFAMILY B ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)
								                                where A.database_name=''' + @database +
						                                ''') AS SUB
					                                GROUP BY [TYPE],DATABASE_NAME
					
				                                ) AS S 
				                                PIVOT
				                                (
					                                min(BACKUP_DATE)
					                                FOR BackupType IN([Full] ,[Differential],[Log])
				                                ) AS P

                                BEGIN TRY

	                                IF (not '''+ @state + ''' = ''OFFLINE'')
	                                BEGIN

		                                insert into #Database_FileDetails
		                                (
			                                DatabaseName,
			                                Drive,
			                                Filegroup,
			                                [File],
			                                SizeinMB,
                                            AutoGrowth,
                                            MaxSize,
                                            GrowthPercent
                                             
		                                )
		                                SELECT	
			                                '''+@database + ''',
			                                Left(df.physical_name,3) AS Drive,
			                                ISNULL(f.name,''LOG'') AS FileGroup,
			                                RIGHT(df.physical_name, CHARINDEX(''\'', REVERSE(df.physical_name))-1) AS [File], 
			                                df.size*8/1024  AS SizeInMb,
                                            df.is_percent_growth AS AutoGrowth,
                                            df.max_size AS MaxSize,
                                            df.growth AS GrowthPercent
		                                FROM         
			                                [' + @database + '].sys.database_files df LEFT OUTER JOIN
			                                [' + @database + '].sys.filegroups f ON df.data_space_id = f.data_space_id  
	                                END	
                                END TRY
                                BEGIN CATCH
                                END CATCH
                                '

                                exec( @query)

                                FETCH NEXT FROM databases_cursor INTO @database, @databaseid, @collation,@create_date ,@owner, @recoveryModel, @state, @CompatibilityLevel,@IsAutoShrink

                                 END
 
                                CLOSE databases_cursor
                                DEALLOCATE databases_cursor

                                SELECT 
	                                D.DatabaseID as DatabaseID,
	                                D.DatabaseName as DatabaseName,
	                                D.Collation as Collation,
	                                D.[Owner] as [Owner],
	                                D.RecoveryModel as RecoveryModel,
	                                D.[State] as [State],
	                                D.CompatibilityLevel as CompatibilityLevel,
                                    D.IsAutoShrink as [AutoShrink],
	                                DatabaseProperty (D.DatabaseName, 'version') as DatabaseVersion,
	                                D.Create_date as CreateDate,
	                                [DF].[File] as [File],
	                                DF.FileGroup as FileGroup,
	                                DF.Drive as Drive,
	                                DF.SizeInMb as SizeInMb,
                                    [DF].[AutoGrowth] as [AutoGrowth],
                                    [DF].[MaxSize] as Max_Growth_Size,
                                    [DF].[GrowthPercent] as Growth_Percentage, 
	                                DB.LatestFullBackup as LatestFullBackup,
	                                DB.LatestDifferentialBackup as LatestDifferentialBackup,
	                                DB.LatestLogBackup as LatestLogBackup 
                                FROM 
	                                #Database AS D 
		                                left outer join 
	                                #Database_FileDetails as DF on D.DatabaseName = DF.DatabaseName  
		                                left outer join 
	                                #Database_BackupDetails as DB on D.DatabaseName=DB.DatabaseName 

                                drop table #Database
                                drop table #Database_FileDetails
                                drop table #Database_BackupDetails






";
            return sql;
        }

        private static void CollectSQLServerServiceInfo(HealthCheckDataContext dc, Service service, string instance)
        {

            try
            {
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillSQLServerServiceInfo: ", " FillSQLServerServiceInfo method in Installation Check Started....");

                using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].Replace("{SQLServerInstance}", service.InstanceName).Replace("{Database}", "master")))
                using (SqlConnection cn1 = new SqlConnection(@"Server=" + instance.ToString() + ";Database=master;Integrated Security=True"))
                {



                    cn.Open();
                    cn1.Open();
                    //enable Ole Automation Procedures using sp_configure
                    string sqlOLE = "sp_configure 'show advanced options',1 ;reconfigure with override;";
                    SqlCommand OLEcmd = new SqlCommand(sqlOLE, cn);
                    OLEcmd.ExecuteNonQuery();

                    string sqlOLE1 = "sp_configure 'Ole Automation Procedures',1 ;reconfigure with override;";
                    SqlCommand OLEcmd1 = new SqlCommand(sqlOLE1, cn);
                    OLEcmd1.ExecuteNonQuery();

                    //enabling  Ole Automation Procedures using sp_configure ends




                    //Get product Version,Edition etc
                    String sql = @"SELECT 
                                    cast(SERVERPROPERTY('Edition' ) as varchar(100)) As Edition,
                                    REPLACE( cast(SERVERPROPERTY('ProductVersion' )as varchar(13)), '.0.','.00.') AS ProductVersion,
                                    SERVERPROPERTY ('productlevel') as Productlevel"
                        ;
                    SqlCommand cmd = new SqlCommand(sql, cn1);
                    SqlDataReader rd = cmd.ExecuteReader();

                    while (rd.Read())
                    {

                        service.ProductVersion = rd["ProductVersion"].ToString();
                        service.Edition = rd["Edition"].ToString();
                        //service.Description = dc.GetDescriptionFromProductVersion(service.ProductVersion);
                        service.Description = rd["Productlevel"].ToString();
                    }

                    rd.Close();
                }

                dc.SubmitChanges();

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillSQLServerServiceInfo: ", " FillSQLServerServiceInfo method in Installation Check Completed....");

            }
            catch (Exception ex)
            {

                errors.Add(ex);

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillSQLServerServiceInfo: ", " Some error occured in Installation Check.Please check exception file for details");

            }


        }

    }
}